package com.example.dbproto;

import java.util.ArrayList;
import java.util.Date;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHandler {
	SQLiteOpenHelper dbHelper;
	SQLiteDatabase db;

	public DBHandler(Context context) {
		this.dbHelper = new DBManager(context);
		this.db = dbHelper.getWritableDatabase();
		// db.execSQL("delete from daily");

	}

	public void testDB() {
		Cursor result = db.rawQuery("select * from daily", null);

		while (result.moveToNext()) {
			Log.e("debug", result.getString(0));
		}
		Log.e("debug", "----------------------------------");
	}

	public void insertDaily(Date date, int soju, int beer, int whisky, int mak, int wine) {
		String sDate = date.getYear() + "-" + intoDateString(date.getMonth() + 1) + "-"
				+ intoDateString(date.getDate());

		db.execSQL("insert or replace into daily values('" + sDate + "'," + soju + "," + beer + "," + whisky + ","
				+ mak + "," + wine + ")");
		// db.rawQuery("insert or replace into daily values('?',?,?,?,?,?);",
		// new String[]{sDate,Integer.toString(soju)});

	}

	public Alcohol getDayAlcohol(Date date) {
		String sDate = date.getYear() + "-" + intoDateString(date.getMonth() + 1) + "-"
				+ intoDateString(date.getDate());

		Cursor result = db.rawQuery("select * from daily where date='" + sDate + "'", null);

		result.moveToNext();
		Alcohol alcol = new Alcohol(result.getString(0), result.getInt(1), result.getInt(2), result.getInt(3),
				result.getInt(4), result.getInt(5));

		return alcol;
	}

	public int getPeriodSeat(Date prevDate, Date nextDate) {
		String sPrevDate = prevDate.getYear() + "-" + intoDateString(prevDate.getMonth() + 1) + "-"
				+ intoDateString(prevDate.getDate());
		String sNextDate = nextDate.getYear() + "-" + intoDateString(nextDate.getMonth() + 1) + "-"
				+ intoDateString(nextDate.getDate());

		Cursor result = db.rawQuery("select count(*) from daily where date between '" + sPrevDate + "' AND '"
				+ sNextDate + "'", null);
		result.moveToNext();
		Log.e("debug", sPrevDate + " " + sNextDate + "query " + result.getCount() + "cnt");

		return result.getInt(0);
	}
	public Alcohol getPeriodAlcohol(Date prevDate, Date nextDate) {
		String sPrevDate = prevDate.getYear() + "-" + intoDateString(prevDate.getMonth() + 1) + "-"
				+ intoDateString(prevDate.getDate());
		String sNextDate = nextDate.getYear() + "-" + intoDateString(nextDate.getMonth() + 1) + "-"
				+ intoDateString(nextDate.getDate());

		Cursor result = db.rawQuery("select * from daily where date between '" + sPrevDate + "' AND '"
				+ sNextDate + "'", null);
		
		Log.e("debug", sPrevDate + " " + sNextDate + "query " + result.getCount() + "cnt");
		
		int soju=0,beer=0,whisky=0,mak=0,wine=0;
		while (result.moveToNext()) {
			soju+=result.getInt(1);
			beer+=result.getInt(2);
			whisky+=result.getInt(3);
			mak+=result.getInt(4);
			wine+=result.getInt(5);
		}		

		return new Alcohol(soju, beer, whisky, mak, wine);
	}
	
	public ArrayList<Alcohol> getListAlcohol(Date prevDate, Date nextDate){
		String sPrevDate = prevDate.getYear() + "-" + intoDateString(prevDate.getMonth() + 1) + "-"
				+ intoDateString(prevDate.getDate());
		String sNextDate = nextDate.getYear() + "-" + intoDateString(nextDate.getMonth() + 1) + "-"
				+ intoDateString(nextDate.getDate());

		Cursor result = db.rawQuery("select * from daily where date between '" + sPrevDate + "' AND '"
				+ sNextDate + "' ORDER BY date asc", null);
		
		Log.e("debug", sPrevDate + " " + sNextDate + "query " + result.getCount() + "cnt");
		
		ArrayList<Alcohol> listAlcol = new ArrayList<Alcohol>();
		while (result.moveToNext()) {
			listAlcol.add(new Alcohol(result.getString(0),result.getInt(1),result.getInt(2),result.getInt(3),result.getInt(4),result.getInt(5)));
		}
		
		return listAlcol;
	}

	public String intoDateString(int day) {
		if (day < 10)
			return "0" + day;
		else
			return Integer.toString(day);
	}

}
